﻿using System;
using System.Collections;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace Payroll
{
    public class sytpayrolldatabase : PayrollDatabase
    {
        private static Hashtable employees = new Hashtable();
        private static Hashtable unionMembers = new Hashtable();
        public void AddEmployee(Employee employee)//添加雇员
        {
            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            string sqlStr;
            string sqlStr1;
            Type a = employee.Classification.GetType();
            sqlStr = "select * from CommissionedEmployee where id ='" + employee.EmpId + " '";
            SqlCeCommand cmd1 = new SqlCeCommand(sqlStr, connection);
            SqlCeDataReader result1 = cmd1.ExecuteReader();
            while (result1.Read())
            {
                MessageBox.Show("员工id重复");
                return;
            }
            result1.Close();
            sqlStr = "select * from SalariedEmployee where id='" + employee.EmpId + "'";
            SqlCeCommand cmd2 = new SqlCeCommand(sqlStr, connection);
            SqlCeDataReader result2 = cmd2.ExecuteReader();
            while (result2.Read())
            {
                MessageBox.Show("员工id重复");
                return;
            }
            result2.Close();
            sqlStr = "select * from Hourlyemployee where id='" + employee.EmpId + "'";
            SqlCeCommand cmd3 = new SqlCeCommand(sqlStr, connection);
            SqlCeDataReader result3 = cmd3.ExecuteReader();
            while (result3.Read())
            {
                MessageBox.Show("员工id重复");
                return;
            }
            result3.Close();
            if (a.Name == "CommissionClassification")
            {
                CommissionClassification Commissioned = (CommissionClassification)employee.Classification;
                sqlStr = "insert into CommissionedEmployee(id ,name,address,baseRate,commissionRate) values ('" + employee.EmpId + "','" + employee.Name + "','" + employee.Address + "','" + Commissioned.BaseRate + "','" + Commissioned.CommissionRate + "')";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
                sqlStr1 = "insert into Summarytable(id,type) values('" + employee.EmpId + "','CommissionedEmployee')";
                SqlCeCommand cmd4 = new SqlCeCommand(sqlStr1, connection);
                int result4 = cmd4.ExecuteNonQuery();
                MessageBox.Show("新增成功");
            }
            else if (a.Name == "SalariedClassification")
            {
                SalariedClassification Salaried = (SalariedClassification)employee.Classification;
                sqlStr = "insert into SalariedEmployee(id ,name,address,salary) values ('" + employee.EmpId + "','" + employee.Name + "','" + employee.Address + "','" + Salaried.Salary + "')";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
                sqlStr1 = "insert into Summarytable(id,type) values('" + employee.EmpId + "','SalariedEmployee')";
                SqlCeCommand cmd4 = new SqlCeCommand(sqlStr1, connection);
                int result4 = cmd4.ExecuteNonQuery();
                MessageBox.Show("新增成功");
            }
            else if (a.Name == "HourlyClassification")
            {
                HourlyClassification Hourly = (HourlyClassification)employee.Classification;
                sqlStr = "insert into Hourlyemployee(id ,name,address,hourlyRate) values ('" + employee.EmpId + "','" + employee.Name + "','" + employee.Address + "','" + Hourly.HourlyRate + "')";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
                sqlStr1 = "insert into Summarytable(id,type) values ('" + employee.EmpId + "','Hourlyemployee')";
                SqlCeCommand cmd4 = new SqlCeCommand(sqlStr1, connection);
                int result4 = cmd4.ExecuteNonQuery();
                MessageBox.Show("新增成功");
            }
            connection.Close();
        }
        public void AddTimeCard(TimeCard card, int id)//考勤表
        {
            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            string sqlStr = "insert into TimeCardtable(id,hours,date) values ('" + id + "','" + card.Hours + "','" + card.Date + "')";
            SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
            int result = cmd.ExecuteNonQuery();
            MessageBox.Show("考勤成功");
        }
        public void AddSalesReceipt(SalesReceipt receipt, int id)//销售记录
        {
            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            string sqlStr = "insert into SalesReceipttable(id,amount,date) values ('" + id + "','" + receipt.SaleAmount + "','" + receipt.Date + "')";
            SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
            int result = cmd.ExecuteNonQuery();
            MessageBox.Show("插入成功");
        }
        public void ChangeName(Employee employee)//修改雇员
        {
            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            string sqlStr;
            Type a = employee.Classification.GetType();
            if (a.Name == "HourlyClassification")
            {
                sqlStr = "update Hourlyemployee set name = '" + employee.Name + "' where id = '" + employee.EmpId + "'";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
                if (result == 1)
                {
                    MessageBox.Show("修改成功");
                }
            }
            else if (a.Name == "CommissionClassification")
            {
                sqlStr = "update CommissionedEmployee set name = '" + employee.Name + "' where id = '" + employee.EmpId + "'";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
                if (result == 1)
                {
                    MessageBox.Show("修改成功");
                }
            }
            else if (a.Name == "SalariedClassification")
            {
                sqlStr = "update SalariedEmployee set name = '" + employee.Name + "' where id = '" + employee.EmpId + "'";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
                if (result == 1)
                {
                    MessageBox.Show("修改成功");
                }
            }
            connection.Close();
        }
        public void ChangeRate(Employee employee)//更改工资
        {
            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            string sqlStr;
            Type a = employee.Classification.GetType();
            if (a.Name == "HourlyClassification")
            {
                HourlyClassification Hourly = (HourlyClassification)employee.Classification;
                sqlStr = "update Hourlyemployee set hourlyRate = '" + Hourly.HourlyRate + "' where id = '" + employee.EmpId + "'";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
            }
            else if (a.Name == "CommissionClassification")
            {
                CommissionClassification Commissioned = (CommissionClassification)employee.Classification;
                sqlStr = "update CommissionedEmployee set baseRate = '" + Commissioned.BaseRate + "',commissionRate = '" + Commissioned.CommissionRate + "' where id = '" + employee.EmpId + "'";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
            }
            else if (a.Name == "SalariedClassification")
            {

                SalariedClassification Salaried = (SalariedClassification)employee.Classification;
                sqlStr = "update SalariedEmployee set salary = '" + Salaried.Salary + "' where id = '" + employee.EmpId + "'";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
            }
            connection.Close();

        }
  
        public Employee GetEmployee(int id)//返回员工信息
        {
            string name = null;
            string address = null;
            double hourlyRate = 0;
            double salary = 0;
            double baseRate = 0;
            double commissionRate = 0;
            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            string sqlStr = " select type from Summarytable where id='" + id + "' ";
            SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
            string type = (string)cmd.ExecuteScalar();
            if (type == "Hourlyemployee")
            {
                string sqlStr1 = "select name from Hourlyemployee where id ='" + id + " '";
                SqlCeCommand cmd1 = new SqlCeCommand(sqlStr1, connection);
                name = (string)cmd1.ExecuteScalar();
                string sqlStr2 = "select address from Hourlyemployee where id ='" + id + " '";
                SqlCeCommand cmd2 = new SqlCeCommand(sqlStr2, connection);
                address = (string)cmd2.ExecuteScalar();
                string sqlStr3 = "select hourlyRate from Hourlyemployee where id ='" + id + " '";
                SqlCeCommand cmd3 = new SqlCeCommand(sqlStr3, connection);
                hourlyRate = (double)cmd3.ExecuteScalar();
            }
            else if (type == "SalariedEmployee")
            {
                string sqlStr1 = "select name from SalariedEmployee where id ='" + id + " '";
                SqlCeCommand cmd1 = new SqlCeCommand(sqlStr1, connection);
                name = (string)cmd1.ExecuteScalar();
                string sqlStr2 = "select address from SalariedEmployee where id ='" + id + " '";
                SqlCeCommand cmd2 = new SqlCeCommand(sqlStr2, connection);
                address = (string)cmd2.ExecuteScalar();
                string sqlStr3 = "select salary from SalariedEmployee where id ='" + id + " '";
                SqlCeCommand cmd3 = new SqlCeCommand(sqlStr3, connection);
                salary = (double)cmd3.ExecuteScalar();
            }
            else if (type == "CommissionedEmployee")
            {
                string sqlStr1 = "select name from CommissionedEmployee where id ='" + id + " '";
                SqlCeCommand cmd1 = new SqlCeCommand(sqlStr1, connection);
                name = (string)cmd1.ExecuteScalar();
                string sqlStr2 = "select address from CommissionedEmployee where id ='" + id + " '";
                SqlCeCommand cmd2 = new SqlCeCommand(sqlStr2, connection);
                address = (string)cmd2.ExecuteScalar();
                string sqlStr3 = "select baseRate from CommissionedEmployee where id ='" + id + " '";
                SqlCeCommand cmd3 = new SqlCeCommand(sqlStr3, connection);
                baseRate = (double)cmd3.ExecuteScalar();
                string sqlStr4 = "select commissionRate from CommissionedEmployee where id ='" + id + " '";
                SqlCeCommand cmd4 = new SqlCeCommand(sqlStr4, connection);
                commissionRate = (double)cmd4.ExecuteScalar();
            }
            else if (type == null)
            {
                MessageBox.Show("id不存在");
                id = 0;
            }
            Employee e = new Employee(id, name, address);
            if (type == "Hourlyemployee")
            {
                e.Classification = new HourlyClassification(hourlyRate);
                HourlyClassification hc = (HourlyClassification)e.Classification;
                sqlStr = " select hours,date  from TimeCardtable where id='" + id + "' ";
                cmd = new SqlCeCommand(sqlStr, connection);
                SqlCeDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    TimeCard tc = new TimeCard(reader.GetDateTime(1), reader.GetDouble(0));
                    hc.AddTimeCard(tc);
                }
                e.Schedule = new WeeklySchedule();
            }
            else if (type == "SalariedEmployee")
            {
                e.Classification = new SalariedClassification(salary);
                e.Schedule = new MonthlySchedule();
            }
            else if (type == "CommissionedEmployee")
            {
                e.Classification = new CommissionClassification(baseRate, commissionRate);
                CommissionClassification hc = (CommissionClassification)e.Classification;
                sqlStr = " select amount,date  from SalesReceipttable where id='" + id + "' ";
                cmd = new SqlCeCommand(sqlStr, connection);
                SqlCeDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    SalesReceipt sr = new SalesReceipt(reader.GetDateTime(1), reader.GetDouble(0));
                    hc.AddSalesReceipt(sr);
                }
                e.Schedule = new BiWeeklySchedule();
            }
            e.Method = new HoldMethod();
            return e;
        }

        public void DeleteEmployee(int id)//删除雇员
        {
            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            string sqlStr;
            string sqlStr1;
            string sqlStr2;
            string sqlStr3;
            string sqlStr4;
            sqlStr = "delete from CommissionedEmployee where id = '" + id + "'";
            SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
            int result = cmd.ExecuteNonQuery();
            sqlStr = "delete from Hourlyemployee where id = '" + id + "'";
            SqlCeCommand cmd2 = new SqlCeCommand(sqlStr, connection);
            int result2 = cmd2.ExecuteNonQuery();
            sqlStr = "delete from SalariedEmployee where id = '" + id + "'";
            SqlCeCommand cmd3 = new SqlCeCommand(sqlStr, connection);
            int result3 = cmd3.ExecuteNonQuery();
            if (result == 0 & result2 == 0 & result3 == 0)
            {
                MessageBox.Show("删除失败：该员工不存在！");
            }
            else
            {
                sqlStr1 = "delete from Summarytable where id = '" + id + "'";
                SqlCeCommand cmd4 = new SqlCeCommand(sqlStr1, connection);
                int result4 = cmd4.ExecuteNonQuery();
                sqlStr2 = "delete from TimeCardtable where id = '" + id + "'";
                SqlCeCommand cmd5 = new SqlCeCommand(sqlStr2, connection);
                int result5 = cmd5.ExecuteNonQuery();
                sqlStr3 = "delete from SalesReceipttable where id = '" + id + "'";
                SqlCeCommand cmd6 = new SqlCeCommand(sqlStr3, connection);
                int result6 = cmd6.ExecuteNonQuery();
                sqlStr4 = "delete from payemployee where id = '" + id + "'";
                SqlCeCommand cmd7 = new SqlCeCommand(sqlStr4, connection);
                int result7 = cmd7.ExecuteNonQuery();
                MessageBox.Show("删除成功！");
            }
            connection.Close();
        }
        public void AddUnionMember(int id, Employee e)
        {
            unionMembers[id] = e;
        }

        public Employee GetUnionMember(int id)
        {
            return unionMembers[id] as Employee;
        }

        public void RemoveUnionMember(int memberId)
        {
            unionMembers.Remove(memberId);
        }

        public ArrayList GetAllEmployeeIds()
        {
            string sqlStr;

            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            sqlStr = " select id,type from Summarytable ";
            SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
            SqlCeDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                //Console.WriteLine(reader[0]);
                int x = Convert.ToInt32(reader[0]);
                Console.WriteLine(x);
                employees[x] = GetEmployee(x);
            }

            return new ArrayList(employees.Keys);
        }

        public IList GetAllEmployees()
        {
            string sqlStr;

            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            sqlStr = " select id,type from Summarytable ";
            SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
            SqlCeDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                employees[reader.GetInt32(0)] = GetEmployee(reader.GetInt32(0));
            }
            return new ArrayList(employees.Values);
        }
        public void pay(int id, double money, DateTime payDate, string payMethod)//薪水支付
        {
            string connString = @"Data Source=..\..\Database1.sdf";
            SqlCeConnection connection = new SqlCeConnection(connString);
            connection.Open();
            if (payMethod == "直接支付")
            {
                string sqlStr = " insert into payemployee (id,payMethods,payDate,pay) values('" + id + "','" + "直接支付" + "','" + payDate + "','" + money + "')";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
            }
            else if (payMethod == "支票")
            {
                string sqlStr = " insert into payemployee (id,payMethods,payDate,pay) values('" + id + "','" + "支票" + "','" + payDate + "','" + money + "')";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
            }
            else if (payMethod == "邮寄")
            {
                string sqlStr = " insert into payemployee (id,payMethods,payDate,pay) values('" + id + "','" + "邮寄" + "','" + payDate + "','" + money + "')";
                SqlCeCommand cmd = new SqlCeCommand(sqlStr, connection);
                int result = cmd.ExecuteNonQuery();
            }

        }
        public void Clear()
        {
            employees.Clear();
            unionMembers.Clear();
        }
    }
}